﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Runtime.Serialization;
using KPIS.GERP.Models.Class;

namespace KPIS.GERP.Models.SYS
{
    [DataContract, Table("SysApproveProcessGroup")]
    public class ApproveProcessGroup
    {
        [Key, DataMember, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int AppPrcGrpId { get; set; }

        [DataMember]
        public string Code { get; set; }

        [DataMember]
        public string Name { get; set; }

        [DataMember]
        public string Description { get; set; }

        [DataMember, DefaultValue(true)]
        public bool UseReferenceEntity { get; set; }

        #region Item


        [DataMember]
        public string Item_TblName { get; set; }

        [DataMember]
        public string Item_ColId { get; set; }

        [DataMember]
        public string Item_ColCode { get; set; }

        [DataMember]
        public string Item_ColName { get; set; }

        [DataMember]
        public string Item_ColRecordStatus { get; set; }

        [DataMember]
        public string Item_ColRecordStatusStatusesUsing { get; set; }
         
        [DataMember]
        public string Item_ColEntityRefId { get; set; }

        #endregion


        #region

        [DataMember]
        public string Entity_TblName { get; set; }

        [DataMember]
        public string Entity_ColId { get; set; }

        [DataMember]
        public string Entity_ColCode { get; set; }

        [DataMember]
        public string Entity_ColName { get; set; }

        [DataMember]
        public string Entity_ColRecordStatus { get; set; }

        [DataMember]
        public string Entity_ColRecordStatusStatusesUsing { get; set; }

        // U,A,NULL = record with status U,A,null will select, other will not

        [DataMember]
        public string Entity_ColAppPrcIdName { get; set; }

        [DataMember]
        public string Entity_ColUrlForEditEntityInGroup { get; set; }

        #endregion

        #region

        [DataMember]
        public bool EntityGroup_IsUse { get; set; }

        [DataMember]
        public string EntityGroup_TblName { get; set; }

        [DataMember]
        public string EntityGroup_ColEntityToGroupId { get; set; }

        [DataMember]
        public string EntityGroup_ColId { get; set; }

        [DataMember]
        public string EntityGroup_ColName { get; set; }

        [DataMember]
        public string EntityGroup_ColCode { get; set; }

        [DataMember]
        public string EntityGroup_ColRecordStatus { get; set; }

        [DataMember]
        public string EntityGroup_ColRecordStatusStatusesUsing { get; set; }

        #endregion

        [DataMember]
        public int? DefaultProcessAppPrcId { get; set; }

        [NotMapped]
        public ApproveProcess DefaultApproveProcess { get { return ApproveProcesses.FirstOrDefault(o => o.RecordStatus != "D" && o.IsDefaultProcess); } }

        [DataMember]
        public ICollection<ApproveProcess> ApproveProcesses { get; set; }

        [NotMapped]
        public List<ReferenceEntity> Entities { get; set; }

        #region

        // Remark: Can be use when connectable to database
        [DataMember, NotMapped]
        public Exception FoundException { get; set; }

        public bool CreateColumnAppPrcIdIfNotExists()
        {
            try
            {
                // Add column appPrcId mapping
                string queryAlterColumn = string.Format(
                    @"
IF NOT EXISTS(
    SELECT TOP 1 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE 
    [TABLE_NAME] = '{0}'
    AND [COLUMN_NAME] = '{1}')
BEGIN
    ALTER TABLE [{0}]
    ADD [{1}] INT NULL
END",
                    Entity_TblName,
                    Entity_ColAppPrcIdName);
                using (var context = new GERPContext())
                {
                    context.Database.ExecuteSqlCommand(queryAlterColumn);
                }
                return true;
            }
            catch (Exception ex)
            {
                FoundException = ex;
                return false;
            }
        }

        public void SetAppPrcIdToEntity(int? appPrcId, int entityId)
        {
            try
            {
                CreateColumnAppPrcIdIfNotExists();

                using (var context = new GERPContext(false))
                {
                    string query = "";


                    query = string.Format(
                        @"
UPDATE [dbo].[{0}]
   SET {1} = {2}
 WHERE {3} = {4}
",
                        Entity_TblName,
                        Entity_ColAppPrcIdName,
                        appPrcId.HasValue?""+appPrcId:"NULL",
                        Entity_ColId,
                        entityId);

                    context.Database.ExecuteSqlCommand(query);
                }

            }
            catch (Exception ex)
            {
                FoundException = ex;
            }
        }

        string query = "", queryEntWhere = "", queryEntGrpWhere = "";
        public void LoadMoreInformation()
        {
            Entities = new List<ReferenceEntity>();
            try
            {
                using (var context = new GERPContext(false))
                {
                    // Collect where record status of entity, entity group
                    string[] entUsingStatus;
                    if ((entUsingStatus = Entity_ColRecordStatusStatusesUsing.SplitExcludeEmpty(',')).Any())
                    {
                        queryEntWhere = string.Format(
                            " entityTbl.{0} in ({1}) ",
                            Entity_ColRecordStatus,
                            string.Join(", ", entUsingStatus.Select(us => CompareUtility.IsStringsEqual(us, "null") ? "NULL" : "'" + us + "'")));
                    }
                    var entGrpUsingStatus = this.EntityGroup_ColRecordStatusStatusesUsing.SplitExcludeEmpty(',');
                    if ((entGrpUsingStatus = this.EntityGroup_ColRecordStatusStatusesUsing.SplitExcludeEmpty(',')) != null)
                    {
                        queryEntGrpWhere = string.Format(
                            "  entityGrpTbl.{0} in ({1}) ",
                            EntityGroup_ColRecordStatus,
                            string.Join(", ", entGrpUsingStatus.Select(us => CompareUtility.IsStringsEqual(us, "null") ? "NULL" : "'" + us + "'")));
                    }

                    CreateColumnAppPrcIdIfNotExists();

                    // Query select data
                    if (!EntityGroup_IsUse)
                    {
                        query = string.Format(
                            @"
SELECT 
    {0} as Id, 
    CONCAT(coalesce({1},''), ' - ',coalesce({2},'')) as Name, 
    {3} as AppPrcId
FROM {4} as entityTbl ",
                            " entityTbl." + Entity_ColId,
                            (!string.IsNullOrWhiteSpace(Entity_ColCode) ? " entityTbl." + Entity_ColCode + "" : "''"),
                            " entityTbl." + Entity_ColName,
                            " entityTbl." + Entity_ColAppPrcIdName,
                            Entity_TblName);
                    }
                    else
                    {
                        query = string.Format(
                            @"
SELECT 
    {0} as Id, 
    '[' + coalesce({2},'') + '] [' + coalesce({4},'') + ']' as Name, 
    CONCAT(coalesce({1},''), coalesce({2},''), ' [', coalesce({3},''), ' - ', coalesce({4},''),']') as NameOld, 
    {5} as AppPrcId
FROM {6} as entityTbl 
    INNER JOIN {7} as entityGrpTbl on {8} = {9} 
              {10}
    ",
                            " entityTbl." + Entity_ColId,
                            (!string.IsNullOrWhiteSpace(EntityGroup_ColCode) ? "entityGrpTbl." + EntityGroup_ColCode : "''"),
                            " entityGrpTbl." + EntityGroup_ColName,
                            (!string.IsNullOrWhiteSpace(Entity_ColCode) ? " entityTbl." + Entity_ColCode + "" : "''"),
                            " entityTbl." + Entity_ColName,
                            " entityTbl." + Entity_ColAppPrcIdName,
                            Entity_TblName,
                            EntityGroup_TblName,
                            " entityGrpTbl." + EntityGroup_ColId,
                            " entityTbl." + EntityGroup_ColEntityToGroupId,
                            (!string.IsNullOrWhiteSpace(queryEntGrpWhere) ? " and " + queryEntGrpWhere : ""));
                    }

                    if (!string.IsNullOrWhiteSpace(query))
                    {
                        query += " where " + queryEntWhere;
                    }

                    if(!string.IsNullOrWhiteSpace(EntityGroup_ColName))
                    {
                        query += " ORDER BY entityGrpTbl." + EntityGroup_ColName;
                    }

                    // Bind to property Entities of this class
                    IEnumerable<ReferenceEntity> result = context.Database.SqlQuery<ReferenceEntity>(query);
                    Entities = result.ToList();

                    // Bind Approve Process (sub class)
                    var approveProcesses = context.ApproveProcesses.Where(o => o.RecordStatus != "D");
                    foreach (var usingInRefEnt in Entities)
                    {
                        usingInRefEnt.ApproveProcess = approveProcesses.FirstOrDefault(o => o.AppPrcId == usingInRefEnt.AppPrcId);
                    }
                }

                // FoundException is use for check is error while query
                FoundException = null;
            }
            catch (Exception ex)
            {
                FoundException = ex;
            }
        }
        #endregion

        #region

        [DataMember]
        public string RecordStatus { get; set; }

        [DataMember]
        public int? CreatedBy { get; set; }

        [DataMember]
        public DateTime? CreatedWhen { get; set; }

        [DataMember]
        public int? UpdatedBy { get; set; }

        [DataMember]
        public DateTime? UpdatedWhen { get; set; }

        #endregion

    }

    public class ReferenceEntity
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int? AppPrcId { get; set; }
        public ApproveProcess ApproveProcess { get; set; }
    }
}